﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
//using System.Data.SQLite;
using Mono.Data.Sqlite;

namespace Rebides
{
    class Rebides
    {
        static void Main(string[] args)
        {
            if (File.Exists("rebidesStatistics.txt"))
                File.Delete("rebidesStatistics.txt");

            StreamWriter file = new System.IO.StreamWriter("rebidesStatistics.txt", true);

            Rebides rebides = new Rebides();

            DateTime t_inicio;
            DateTime t_fim;
            TimeSpan t_diferenca;

            t_inicio = DateTime.Now;

            Console.WriteLine("Number of Teachers Per Year");
            rebides.teacherPerY(file, "Number of Teachers Per Year");

            Console.WriteLine("Number of Teachers Per Institution Per Year");
            rebides.teacherPerInstPerY(file, "Number of Teachers Per Institution Per Year");

            Console.WriteLine("Number of Teachers Per Degree Per Year");
            rebides.teacherPerDegreePerY(file, "Number of Teachers Per Degree Per Year");

            Console.WriteLine("Number of Teachers Per Institution Per Degree Per Year");
            rebides.teacherDIY(file, "Number of Teachers Per Institution Per Degree Per Year");

            Console.WriteLine("Number of Teacher per a specific Degree Per Institution Per Year"); //In this Case the degree that starts with 'Do'
            rebides.nTPerADegreePerInst(file, "Number of Teacher per a specific Degree Per Institution Per Year", "Do%");

            Console.WriteLine("Set of Teachers per a specific Degree Per Institution Per Year");
            rebides.setOfTPerADegreePerInst(file, "Number of Teacher per a specific Degree Per Institution Per Year", "Do%");

            Console.WriteLine("List Of Institutions Per Year");
            rebides.listInstPerY(file, "List Of Institutions Per Year");

            Console.WriteLine("List Of Holders of a degree Per Year");
            rebides.listHDegreePerY(file, "List Of Holders of a degree Per Year");

            Console.WriteLine("Teachers Hirings per Institution Per Year");
            rebides.teacherHirings(file, "Teachers Hirings");

            Console.WriteLine("Number And set of teachers that changed from one establishment to another per year");
            rebides.teacherInstChange(file, "set of teachers that changed from one establishment to another per year");

            Console.WriteLine("Names of teachers promoted to the next category each year per establishment");
            rebides.promotedTeachers(file, "Names of teachers promoted to the next category each year per establishment");
            

            t_fim = DateTime.Now;
            t_diferenca = t_fim.Subtract(t_inicio);

            file.Close();

            Console.WriteLine("Tempo de Execução - " + t_diferenca);

            Console.WriteLine("FINISH");
            Console.Read();


        }

        //Number of Teacher per Year
        void teacherPerY(StreamWriter file, string title)
        {
            IDataReader reader;
            List<DataSave> list = new List<DataSave>();

            for (int i = 0; i < 10; i++)
            {
                string sql =
                    "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE informatica_registodocencia.ano = " + i;

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    list.Add(new DataSave(reader.GetInt32(0), (2000 + i)));
                }
            }

            //sort by number of teachers
            list.Sort(delegate(DataSave d1, DataSave d2) { return d1.number1.CompareTo(d2.number1); });

            writePrintList(list, title, file);
        }

        //Number of Teacher per Institutions Per Year
        void teacherPerInstPerY(StreamWriter file, string title)
        {
            IDataReader reader;

            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();

            for (int i = 0; i < 10; i++)
            {
                List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT informatica_estabelecimento.designacao, COUNT(docente_id) FROM informatica_registodocencia " +
                              "INNER JOIN informatica_estabelecimento on " +
                              "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                              "WHERE informatica_registodocencia.ano = " + i + " GROUP BY informatica_estabelecimento.designacao";

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    listDataSave.Add(new DataSave(reader.GetString(0), reader.GetInt32(1)));
                }

                //Sort by number of teachers
                listDataSave.Sort(delegate(DataSave d1, DataSave d2) { return d1.number1.CompareTo(d2.number1); });
                finalDic.Add((2000 + i), listDataSave);
            }

            writePrint(finalDic, "Number of Teachers Per Institution Per Year", file);
        }

        //Number of Teacher per Degree Per Year
        void teacherPerDegreePerY(StreamWriter file, string title)
        {
            IDataReader reader;

            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();

            for (int i = 0; i < 10; i++)
            {
                List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT informatica_grau.designacao, COUNT(DISTINCT docente_id) " +
                              "FROM informatica_registodocencia " +
                              "INNER JOIN informatica_grau on informatica_registodocencia.grau_id = informatica_grau.id " +
                              "WHERE informatica_registodocencia.ano = " + i +
                              " GROUP BY informatica_grau.designacao";

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    listDataSave.Add(new DataSave(reader.GetString(0), reader.GetInt32(1)));
                }

                //Sort by number of teachers
                listDataSave.Sort(delegate(DataSave d1, DataSave d2) { return d1.number1.CompareTo(d2.number1); });
                finalDic.Add((2000 + i), listDataSave);
            }

            writePrint(finalDic, "Number of Teachers Per Degree Per Year", file);
        }

        //Number of Teacher per Degree Per Instituition Per Year
        void teacherDIY(StreamWriter file, string title)
        {
            IDataReader reader;
            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();

            for (int i = 0; i < 10; i++)
            { 
                List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT informatica_estabelecimento.designacao, informatica_grau.designacao, COUNT(DISTINCT docente_id) " +
                                "FROM informatica_registodocencia INNER JOIN informatica_grau on " +
                                "informatica_registodocencia.grau_id = informatica_grau.id " +
                                "INNER JOIN informatica_estabelecimento on " +
                                "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                                "WHERE informatica_registodocencia.ano = " + i +
                                " GROUP BY informatica_estabelecimento.designacao, informatica_grau.designacao";

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    listDataSave.Add(new DataSave(reader.GetString(0), reader.GetString(1),reader.GetInt32(2)));
                }

                //Sort by number of teachers
                listDataSave.Sort(delegate(DataSave d1, DataSave d2) { return d1.number1.CompareTo(d2.number1); });

                finalDic.Add((2000 + i), listDataSave);
            }

            writePrint(finalDic, "Number of Teachers Per Degree Per Institution Per Year", file);
        }

        //Number of Teacher per a specific Degree Per Institution Per Year
        void nTPerADegreePerInst(StreamWriter file, string title, string degree)
        {
            IDataReader reader;

            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();

            for (int i = 0; i < 10; i++)
            {
                List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT informatica_estabelecimento.designacao, COUNT(DISTINCT docente_id) " +
                              "FROM informatica_registodocencia " +
                              "INNER JOIN informatica_grau on informatica_registodocencia.grau_id = informatica_grau.id " +
                              "INNER JOIN informatica_estabelecimento on " +
                              "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                              "WHERE informatica_registodocencia.ano = " + i + " and informatica_grau.designacao like '" + degree + "'" +
                              " GROUP BY informatica_estabelecimento.designacao";

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    listDataSave.Add(new DataSave(reader.GetString(0), reader.GetInt32(1)));
                }

                //Sort by number of teachers
                listDataSave.Sort(delegate(DataSave d1, DataSave d2) { return d1.number1.CompareTo(d2.number1); });
                finalDic.Add((2000 + i), listDataSave);
            }

            writePrint(finalDic, "Number of Teacher per a specific Degree Per Institution Per Year", file);
        }

        //Set of Teacher per a specific Degree Per Institution Per Year
        void setOfTPerADegreePerInst(StreamWriter file, string title, string degree)
        {
            IDataReader reader;

            //Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();
            Dictionary<int, List<DataSave>> tmpInst = this.listInstPerYNoPrint(0);
            Dictionary<int, Dictionary<string, List<DataSave>>> finalDic = new Dictionary<int, Dictionary<string, List<DataSave>>>();

            //Put the year on the dictionary
            for (int i = 0; i < 10; i++)
            {
                finalDic.Add(i, new Dictionary<string, List<DataSave>>());
            }

            //put the institutions by year int the dictionary
            foreach (KeyValuePair<int, List<DataSave>> tmp in tmpInst)
            {
                foreach (DataSave data in tmp.Value)
                {
                    finalDic[tmp.Key].Add(data.text1, new List<DataSave>());
                }
            }

            for (int i = 0; i < 10; i++)
            {
                //List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT informatica_estabelecimento.designacao, informatica_docente.nome " +
                              "FROM informatica_registodocencia " +
                              "INNER JOIN informatica_grau on informatica_registodocencia.grau_id = informatica_grau.id " +
                              "INNER JOIN informatica_docente on informatica_registodocencia.docente_id = informatica_docente.id " +
                              "INNER JOIN informatica_estabelecimento on " +
                              "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                              "WHERE informatica_registodocencia.ano = " + i + " and informatica_grau.designacao like '" + degree + "'" +
                              " GROUP BY informatica_docente.nome";

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    finalDic[i][reader.GetString(0)].Add(new DataSave(reader.GetString(1)));
                    //listDataSave.Add(new DataSave(reader.GetString(0), reader.GetString(1)));
                }
            }

            //sort the names of the teachers alphabeticaly
            foreach (KeyValuePair<int, Dictionary<string, List<DataSave>>> dic1 in finalDic)
            {
                foreach (KeyValuePair<string, List<DataSave>> dic2 in dic1.Value)
                {
                    dic2.Value.Sort(delegate(DataSave d1, DataSave d2) { return d1.text1.CompareTo(d2.text1); });
                }
            }

            writePrintDoubleDic(finalDic, "Set of Teacher per a specific Degree Per Institution Per Year", file);
        }

        //List of Institution Per Year
        void listInstPerY(StreamWriter file, string title)
        {
            IDataReader reader;

            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();

            for (int i = 0; i < 10; i++)
            {
                List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT designacao FROM informatica_estabelecimento WHERE ano = " + i;

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    listDataSave.Add(new DataSave(reader.GetString(0)));
                }

                //Sort by name of Institution
                listDataSave.Sort(delegate(DataSave d1, DataSave d2) { return d1.text1.CompareTo(d2.text1); });
                finalDic.Add((2000 + i), listDataSave);
            }

            writePrint(finalDic, "List Of Institutions Per Year", file);
        }

        //List of Holders of a degree Per Year
        void listHDegreePerY(StreamWriter file, string title)
        {
            IDataReader reader;

            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();

            for (int i = 0; i < 10; i++)
            {
                List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT nome FROM informatica_docente WHERE ano = " + i;

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    listDataSave.Add(new DataSave(reader.GetString(0)));
                }

                //Sort by Teachers name
                listDataSave.Sort(delegate(DataSave d1, DataSave d2) { return d1.text1.CompareTo(d2.text1); });
                finalDic.Add((2000 + i), listDataSave);
            }

            writePrint(finalDic, "List Of Holders of a degree Per Year", file);
        }

        //Number of Teacher Hirings Per Institutions Per Year
        void teacherHirings(StreamWriter file, string title)
        {
            IDataReader reader;

            //Dictionary<string, List<DataSave>> instDic = new Dictionary<string, List<DataSave>>();
            Dictionary<string, List<DataSave>> tmpDic = new Dictionary<string, List<DataSave>>();
            Dictionary<string, List<DataSave>> finalDic = new Dictionary<string, List<DataSave>>();

            int tmpNumber = 0;//Save the previous year to make the calculation with the next year


            //Fill a dictionary with all Institutions
            string sql = "SELECT DISTINCT informatica_estabelecimento.designacao " +
                         "FROM informatica_registodocencia " +
                         "INNER JOIN informatica_estabelecimento on " +
                         "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id";

            reader = this.connectBD(sql);
            while (reader.Read())
            {
                tmpDic.Add(reader.GetString(0), new List<DataSave>());
                finalDic.Add(reader.GetString(0), new List<DataSave>());
            }
            reader.Dispose();
            reader = null;

            //Number of teacher per institution per Year
            string sql1 = "SELECT DISTINCT(informatica_estabelecimento.designacao), COUNT(docente_id), informatica_registodocencia.ano " +
                         "FROM informatica_registodocencia INNER JOIN informatica_estabelecimento on " +
                         "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                         "GROUP BY informatica_estabelecimento.designacao, informatica_registodocencia.ano";

            reader = this.connectBD(sql1);
            while (reader.Read())
            {
                tmpDic[reader.GetString(0)].Add(new DataSave(reader.GetInt32(1), reader.GetInt32(2)));
            }

            //Calculate the diference in teachers in every institution per Year
            //Stores information in a new dictionary to be print and save in file
            foreach (KeyValuePair<string, List<DataSave>> value in tmpDic)
            {
                Console.WriteLine(value.Key);
                for (int i = 0; i < 10; i++)
                {
                    DataSave result = value.Value.Find(
                    delegate(DataSave bk)
                    {
                        return bk.number2 == i; //Find an object with year = i
                    }
                    );
                    if (result != null)
                    {
                        //Add the value to the dictionary, copy the new value to tmpNumber to use next year
                        finalDic[value.Key].Add(new DataSave((result.number1 - tmpNumber),(2000 + i)));
                        tmpNumber = result.number1;
                    }
                    else
                    {
                        finalDic[value.Key].Add(new DataSave(("This Institution Not Exist in this year"),(2000 + i)));
                        tmpNumber = 0;
                    }
                }
            }

            writePrintString(finalDic, "Teachers Hirings Per Institution", file);
        }

        //set of teachers that changed from one establishment to another
        void teacherInstChange(StreamWriter file, string title)
        {
            IDataReader reader;
            Dictionary<string, List<DataSave>> tmpDic = new Dictionary<string, List<DataSave>>();
            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();
            string tmpInst = "";

            //fill the finalDic with the year
            for (int i = 1; i < 10; i++)
            {
                finalDic.Add(i, new List<DataSave>());
            }

            //Return the names of all teachers in BD
            string sql = "SELECT DISTINCT(informatica_docente.nome) " +
                          "FROM informatica_registodocencia INNER JOIN informatica_docente on  " +
                          "informatica_docente.id = informatica_registodocencia.docente_id";

            reader = this.connectBD(sql);
            while (reader.Read())
            {
                tmpDic.Add(reader.GetString(0), new List<DataSave>());
            }
            reader.Dispose();
            reader = null;

            //Return Teacher name, Institution and the year
            string sql1 = "SELECT DISTINCT(informatica_docente.nome), informatica_estabelecimento.designacao, " +
                          "informatica_registodocencia.ano " +
                          "FROM informatica_registodocencia INNER JOIN informatica_estabelecimento on " +
                          "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id INNER JOIN " +
                          "informatica_docente on informatica_docente.id = informatica_registodocencia.docente_id " +
                          "GROUP BY informatica_docente.nome, informatica_registodocencia.ano";

            reader = this.connectBD(sql1);
            while (reader.Read())
            {
                tmpDic[reader.GetString(0)].Add(new DataSave(reader.GetString(1), reader.GetInt32(2)));
            }

            //Check each the instituiton of each teacher each year
            //Stores information in a new dictionary to be print and save in file
            foreach (KeyValuePair<string, List<DataSave>> value in tmpDic)
            {
                for (int i = 0; i < 10; i++)
                {
                    DataSave result = value.Value.Find(
                    delegate(DataSave bk)
                    {
                        return bk.number1 == i; //Compare the year
                    }
                    );
                    if (result != null)//if there is a result
                    {

                        if (tmpInst != "")//Get the first year Institution
                        {
                            if (result.text1 != tmpInst)//if is a diferent institution save the teacher name
                            {
                                finalDic[result.number1].Add(new DataSave((value.Key)));
                                tmpInst = result.text1;
                            }
                        }
                        else
                        {
                            tmpInst = result.text1;
                        }
                    }
                    else
                    {
                        //Do Nothing
                    }
                }
                tmpInst = "";
            }

            //sort the names of the teachers alphabeticaly
            foreach (KeyValuePair<int, List<DataSave>> values in finalDic)
            {
                values.Value.Sort(delegate(DataSave d1, DataSave d2) { return d1.text1.CompareTo(d2.text1); });
            }

            //Print and Save in a File
            writePrint(finalDic, title, file);

            //Number of teachers Migrating From one establisment to another
            NOfMigratingTeachY(finalDic, "Number of teachers Migrating From one establisment to another Per Year", file);
        }

        //Number of teachers Migrating From one establisment to another
        //Receives the dictionary with the information from method teacherInstChange
        void NOfMigratingTeachY(Dictionary<int, List<DataSave>> dic, string title, StreamWriter file)
        {
            List<DataSave> list = new List<DataSave>();
            foreach (KeyValuePair<int, List<DataSave>> values in dic)
            {
                list.Add(new DataSave(values.Value.Count, (2000 + values.Key)));
            }

            //Sort the list by the lowest value of number of teacher migrating to the max value
            list.Sort(delegate(DataSave d1, DataSave d2) { return d1.number1.CompareTo(d2.number1); });

            writePrintList(list, title, file);
        }

        //Teachers Promoted to the next category per institutions per year
        void promotedTeachers(StreamWriter file, string title)
        {
            IDataReader reader;
            Dictionary<string, Dictionary<string, List<DataSave>>> tmpDic = new Dictionary<string, Dictionary<string, List<DataSave>>>();
            Dictionary<int, Dictionary<string, List<DataSave>>> finalDic = new Dictionary<int, Dictionary<string, List<DataSave>>>();
            Dictionary<int,List<DataSave>> tmpInst =  this.listInstPerYNoPrint(1);

            string tmpCat = "";

            //Put the year on the dictionary
            for (int i = 1; i < 10; i++)
            {
                finalDic.Add(i, new Dictionary<string, List<DataSave>>());
            }
            //put the institutions by year int the dictionary
            foreach (KeyValuePair<int, List<DataSave>> tmp in tmpInst)
            {
                foreach(DataSave data in tmp.Value)
                {
                    finalDic[tmp.Key].Add(data.text1, new List<DataSave>());
                }
            }

            //Return the names of all intitutions in BD
            string sql = "SELECT DISTINCT(informatica_estabelecimento.designacao) " +
                         "FROM informatica_registodocencia INNER JOIN informatica_estabelecimento on " +
                         "informatica_estabelecimento.id = informatica_registodocencia.estabelecimento_id";

            reader = this.connectBD(sql);
            while (reader.Read())
            {
                tmpDic.Add(reader.GetString(0), new Dictionary<string, List<DataSave>>());
            }
            reader.Dispose();
            reader = null;

            //Teachers per institution of all years
            string sql1 = "SELECT DISTINCT(informatica_estabelecimento.designacao), informatica_docente.nome " +
                          "FROM informatica_registodocencia " +
                          "INNER JOIN informatica_estabelecimento on " +
                          "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                          "INNER JOIN informatica_docente on informatica_docente.id = informatica_registodocencia.docente_id " +
                          "GROUP BY informatica_estabelecimento.designacao, informatica_docente.nome";

            reader = this.connectBD(sql1);
            while (reader.Read())
            {
                tmpDic[reader.GetString(0)].Add(reader.GetString(1), new List<DataSave>());
            }
            reader.Dispose();
            reader = null;

            //Return Teacher name, Institution and the year
            string sql2 = "SELECT DISTINCT(informatica_estabelecimento.designacao), informatica_docente.nome, " +
                          "informatica_categoria.designacao, informatica_registodocencia.ano " +
                          "FROM informatica_registodocencia INNER JOIN informatica_estabelecimento on " +
                          "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id INNER JOIN " +
                          "informatica_docente on informatica_docente.id = informatica_registodocencia.docente_id " +
                          "INNER JOIN informatica_categoria on " +
                          "informatica_categoria.id = informatica_registodocencia.categoria_id " +
                          "GROUP BY informatica_estabelecimento.designacao, informatica_docente.nome, " +
                          "informatica_registodocencia.ano";

            reader = this.connectBD(sql2);
            while (reader.Read())
            {
                tmpDic[reader.GetString(0)][reader.GetString(1)].Add(new DataSave(reader.GetString(2), reader.GetInt32(3)));
            }

            //Check in each the instituiton the teachers that change category each year
            //Stores information in a new dictionary to be print and save in file
            foreach (KeyValuePair<string, Dictionary<string, List<DataSave>>> dic in tmpDic)
            {
                foreach(KeyValuePair<string, List<DataSave>> dic1 in dic.Value)
                {
                    for (int i = 0; i < 10; i++)
                    {
                        DataSave result = dic1.Value.Find(
                        delegate(DataSave bk)
                        {
                            return bk.number1 == i; //Compare the year
                        }
                        );
                        if (result != null)//if there is a result
                        {
                            if (tmpCat != "")//Get the first category of the teacher on that institution
                            {
                                if (result.text1 != tmpCat)//if is a diferent category save the teacher name
                                {
                                    finalDic[result.number1][dic.Key].Add(new DataSave(dic1.Key));
                                    tmpCat = result.text1;
                                }
                            }
                            else
                            {
                                tmpCat = result.text1;
                            }
                        }
                        else
                        {
                            //Do Nothing
                        }
                    }
                    tmpCat = "";
                } 
            }

            //sort the names of the teachers alphabeticaly
            foreach (KeyValuePair<int, Dictionary<string, List<DataSave>>> dic1 in finalDic)
            {
                foreach(KeyValuePair<string, List<DataSave>> dic2 in dic1.Value)
                {
                    dic2.Value.Sort(delegate(DataSave d1, DataSave d2) { return d1.text1.CompareTo(d2.text1); });
                }
            }

            //Print and Save in a File
            writePrintDoubleDic(finalDic, title, file);

            //Number of Promoted teachers per establisment per Year
            NOfPromTeachPerY(finalDic, "Number of Promoted teachers per establisment per Year", file);
        }

        //Number of Promoted teachers per establisment per Year
        //Receives the dictionary with the information from method teacherInstChange
        void NOfPromTeachPerY(Dictionary<int, Dictionary<string, List<DataSave>>> dic, string title, StreamWriter file)
        {
            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int,List<DataSave>>();

            foreach (KeyValuePair<int, Dictionary<string, List<DataSave>>> dic1 in dic)
            {
                finalDic.Add((dic1.Key + 2000), new List<DataSave>());
                foreach (KeyValuePair<string, List<DataSave>> dic2 in dic1.Value)
                {
                    if (dic2.Value.Count != 0)
                    {
                        finalDic[dic1.Key + 2000].Add(new DataSave(dic2.Key, dic2.Value.Count));
                    }
                }
            }

            //Sort the list by the lowest value of number of teacher Promoted per institution per year to the max value
            foreach (KeyValuePair<int, List<DataSave>> dic1 in finalDic)
            {
                dic1.Value.Sort(delegate(DataSave d1, DataSave d2) { return d1.number1.CompareTo(d2.number1); });
            }

            writePrint(finalDic, title, file);
        }

        //List of Institution Per Year without print
        //Used by PromotedTeachers
        Dictionary<int, List<DataSave>> listInstPerYNoPrint(int startYear)
        {
            IDataReader reader;

            Dictionary<int, List<DataSave>> finalDic = new Dictionary<int, List<DataSave>>();

            for (int i = startYear; i < 10; i++)
            {
                List<DataSave> listDataSave = new List<DataSave>();
                string sql = "SELECT designacao FROM informatica_estabelecimento WHERE ano = " + i;

                reader = this.connectBD(sql);
                while (reader.Read())
                {
                    listDataSave.Add(new DataSave(reader.GetString(0)));
                }

                listDataSave.Sort(delegate(DataSave d1, DataSave d2) { return d1.text1.CompareTo(d2.text1); });
                finalDic.Add((i), listDataSave);
            }

            return finalDic;
        }

        //Connect to database and do the query
        //Return the reader with information
        IDataReader connectBD(string sql)
        {
            string connectionString = "URI=file:db/pristine.db, version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            return reader;
        }

        //Prints a dictionary<int, List<DataSave>>
        //Write on a file
        void writePrint(Dictionary<int, List<DataSave>> dic, String title, StreamWriter file)
        {

            file.WriteLine();
            file.WriteLine("##############################################################");
            file.WriteLine(title);
            file.WriteLine("##############################################################");

            foreach (KeyValuePair<int, List<DataSave>> dic1 in dic)
            {
                file.WriteLine();
                Console.WriteLine(dic1.Key);
                file.WriteLine(dic1.Key);
                foreach (DataSave data in dic1.Value)
                {
                    Console.WriteLine(data.ToString());
                    file.WriteLine(data.ToString());
                }

            }
        }

        //Prints a dictionary<string, List<DataSave>>
        //Write on a file
        void writePrintString(Dictionary<string, List<DataSave>> dic, String title, StreamWriter file)
        {

            file.WriteLine();
            file.WriteLine("##############################################################");
            file.WriteLine(title);
            file.WriteLine("##############################################################");

            foreach (KeyValuePair<string, List<DataSave>> dic1 in dic)
            {
                file.WriteLine();
                Console.WriteLine(dic1.Key);
                file.WriteLine(dic1.Key);
                foreach (DataSave data in dic1.Value)
                {
                    Console.WriteLine(data.ToString());
                    file.WriteLine(data.ToString());
                }

            }
        }

        //Prints a List<DataSave>
        //Write on a file
        void writePrintList(List<DataSave> list, String title, StreamWriter file)
        {
            file.WriteLine();
            file.WriteLine("##############################################################");
            file.WriteLine(title);
            file.WriteLine("##############################################################");
            file.WriteLine();

            foreach (DataSave data in list)
            {
                Console.WriteLine(data.ToString());
                file.WriteLine(data.ToString());
            }
        }

        //Prints a dictionary<int, dictionary<string, List<DataSave>>>
        //Write on a file
        void writePrintDoubleDic(Dictionary<int, Dictionary<string, List<DataSave>>> dic, String title, StreamWriter file)
        {

            file.WriteLine();
            file.WriteLine("##############################################################");
            file.WriteLine(title);
            file.WriteLine("##############################################################");

            foreach (KeyValuePair<int, Dictionary<string, List<DataSave>>> dic1 in dic)
            {
                file.WriteLine();
                Console.WriteLine(2000 + dic1.Key);
                file.WriteLine(2000 + dic1.Key);
                foreach (KeyValuePair<string, List<DataSave>> dic2 in dic1.Value)
                {
                    file.WriteLine();
                    Console.WriteLine(dic2.Key);
                    file.WriteLine(dic2.Key);
                    foreach (DataSave data in dic2.Value)
                    {
                        Console.WriteLine(data.ToString());
                        file.WriteLine(data.ToString());
                    }
                }

            }
        }
    }
}